package com.freesun.monitor.jm;

import com.freesun.monitor.db.*;
import org.apache.jmeter.config.Arguments;
import org.apache.jmeter.protocol.java.sampler.AbstractJavaSamplerClient;
import org.apache.jmeter.protocol.java.sampler.JavaSamplerContext;
import org.apache.jmeter.samplers.SampleResult;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

/**
 * @author yangfei
 * @desc db操作, jmeter
 * @Created 13-12-23.
 */
public class OperateDb extends AbstractJavaSamplerClient {

    private Logger log = LoggerFactory.getLogger(this.getClass());
    private static long start = 0;
    private static long end = 0;

    private OperateMariaDb operateMariaDb;
    private OperateMysql operateMysql;
    private OperateSqlServer operateSqlServer;
    private OperateOracle operateOracle;
    private OperateSybase operateSybase;

    private String sql;
    private String expected;
    private int type;

    private SampleResult sp = new SampleResult();

    /**
     * 获取参数
     */
    @Override
    public Arguments getDefaultParameters() {
        Arguments args = new Arguments();
        //添加默认参数示例
        //"jdbc:jtds:sqlserver://127.0.0.1:1433/test";  默认实例
        //jdbc:jtds:sybase://192.168.102.100:5000/test
        //jdbc:oracle:thin:@localhost:1521:orcl
        args.addArgument("jdbc", "不同数据库示例:jdbc:mysql://127.0.0.1:3306/mm|jdbc:jtds:sqlserver://127.0.0.1:1433/test|" +
                "jdbc:jtds:sybase://127.0.0.1:5000/test|jdbc:oracle:thin:@127.0.0.1:1521:orcl");
        args.addArgument("dbusername", "请输入用户名");
        args.addArgument("dbpassword", "请输入密码");
        args.addArgument("expected", "查询比对查询结果（非查询该参数无效），查询反馈结果（json串）包含预期结果字符串则返回true，否则返回false");
        args.addArgument("sql", "select * from test01 where id = 1");
        return args;
    }


    /**
     * @param javaSamplerContext
     * @desc 初始化工作
     */
    @Override
    public void setupTest(JavaSamplerContext javaSamplerContext) {

        // 获取jmeter传入参数
        String jdbc = javaSamplerContext.getParameter("jdbc");
        String dbusername = javaSamplerContext.getParameter("dbusername");
        String dbpassword = javaSamplerContext.getParameter("dbpassword");
        sql = javaSamplerContext.getParameter("sql");
        expected = javaSamplerContext.getParameter("expected");

        start = System.currentTimeMillis();
        if (jdbc.toLowerCase().indexOf("mysql") >= 0) {
            operateMysql = new OperateMysql();
            operateMysql.GetConnect(jdbc, dbusername, dbpassword);
            type = 1;
            log.debug("连接mysql数据库...");
            System.out.println("连接mysql数据库...");
        } else if (jdbc.toLowerCase().indexOf("mariadb") >= 0) {
            operateMariaDb = new OperateMariaDb();
            operateMariaDb.GetConnect(jdbc, dbusername, dbpassword);
            type = 2;
            log.debug("连接mariadb数据库...");
            System.out.println("连接mariadb数据库...");
        } else if (jdbc.toLowerCase().indexOf("sqlserver") >= 0) {
            operateSqlServer = new OperateSqlServer();
            operateSqlServer.GetConnect(jdbc, dbusername, dbpassword);
            type = 3;
            log.debug("连接sqlserver数据库...");
            System.out.println("连接sqlserver数据库...");
        } else if (jdbc.toLowerCase().indexOf("oracle") >= 0) {
            operateOracle = new OperateOracle();
            operateOracle.GetConnect(jdbc, dbusername, dbpassword);
            type = 4;
            log.debug("连接oracle数据库...");
            System.out.println("连接oracle数据库...");
        } else if (jdbc.toLowerCase().indexOf("sybase") >= 0) {
            operateSybase = new OperateSybase();
            operateSybase.GetConnect(jdbc, dbusername, dbpassword);
            type = 5;
            log.debug("连接sybase数据库...");
            System.out.println("连接sybase数据库...");
        }

    }


    @Override
    public SampleResult runTest(JavaSamplerContext javaSamplerContext) {

        //采样结果

        sp.setSampleLabel("Java请求哦");
        //采样开始
        sp.sampleStart();
        switch (type) {
            case 1:
                System.out.println("sql:"+sql);
                if (sql.toLowerCase().indexOf("insert".toLowerCase()) >= 0) {
                    System.out.println("连接mysql数据库,insert...");
                    int oi = operateMysql.InsertData(sql);
                    System.out.println("返回值是:" + oi);
                    if (oi > 0) {
                        sp.setSuccessful(true);
                        sp.setResponseMessage(sql + "成功!");
                        System.out.println(sql + "成功!");
                    } else {
                        sp.setSuccessful(false);
                        sp.setResponseMessage(sql + "失败!");
                        System.out.println(sql + "失败!");
                    }
                } else if (sql.toLowerCase().indexOf("delete".toLowerCase()) >= 0) {
                    System.out.println("连接mysql数据库,delete...");
                    int od = operateMysql.DeleteData(sql);
                    System.out.println("返回值是:" + od);
                    if (od > 0) {
                        sp.setSuccessful(true);
                        sp.setResponseMessage(sql + "成功!");
                        System.out.println(sql + "成功!");
                    } else {
                        sp.setSuccessful(false);
                        sp.setResponseMessage(sql + "失败!");
                        System.out.println(sql + "失败!");
                    }
                } else if (sql.toLowerCase().indexOf("select".toLowerCase()) >= 0) {
                    System.out.println("连接mysql数据库,select...");
                    ResultSet rs = operateMysql.SelectData(sql);

                    try {
                        //创建一个JSONArray对象
                        JSONArray jsonArray = new JSONArray();
                        //获得ResultSetMeataData对象
                        ResultSetMetaData rsmd = rs.getMetaData();
                        if (rs.next()) {//当结果集存在时
                            while (rs.next()) {
                                //定义json对象
                                JSONObject obj = new JSONObject();
                                //判断数据类型&获取value
                                getType(rs, rsmd, obj);
                                //将对象添加到JSONArray中
                                jsonArray.put(obj);
                            }
                            System.out.println(jsonArray.toString().toLowerCase());
                            if (jsonArray.toString().toLowerCase().indexOf(expected) >= 0) {
                                sp.setSuccessful(true);
                                sp.setResponseMessage(sql + "成功!");
                                System.out.println(sql + "成功!");
                            } else {
                                sp.setSuccessful(false);
                                sp.setResponseMessage(sql + "失败!");
                                System.out.println(sql + "失败!");
                            }
                        } else {
                            sp.setSuccessful(false);
                            sp.setResponseMessage(sql + "查询为空!");
                        }
                    } catch (SQLException e) {
                        log.error("判断结果集是否为空时失败!");
                        e.printStackTrace();
                    }
                } else if (sql.toLowerCase().indexOf("update".toLowerCase()) >= 0) {
                    System.out.println("连接mysql数据库,update...");
                    int ou = operateMysql.UpdateData(sql);
                    System.out.println("返回值是:" + ou);
                    if (ou != -1) {
                        sp.setSuccessful(true);
                        sp.setResponseMessage(sql + "成功!");
                        System.out.println(sql + "成功!");
                    } else {
                        sp.setSuccessful(false);
                        sp.setResponseMessage(sql + "失败!");
                        System.out.println(sql + "失败!");
                    }
                }
                break;
            case 2:
                System.out.println("sql:"+sql);
                if (sql.toLowerCase().indexOf("insert".toLowerCase()) >= 0) {
                    System.out.println("连接mariadb数据库,insert...");
                    int oi = operateMariaDb.InsertData(sql);
                    System.out.println("返回值是:" + oi);
                    if (oi > 0) {
                        sp.setSuccessful(true);
                        sp.setResponseMessage(sql + "成功!");
                        System.out.println(sql + "成功!");
                    } else {
                        sp.setSuccessful(false);
                        sp.setResponseMessage(sql + "失败!");
                        System.out.println(sql + "失败!");
                    }
                } else if (sql.toLowerCase().indexOf("delete".toLowerCase()) >= 0) {
                    System.out.println("连接mariadb数据库,delete...");
                    int od = operateMariaDb.DeleteData(sql);
                    System.out.println("返回值是:" + od);
                    if (od > 0) {
                        sp.setSuccessful(true);
                        sp.setResponseMessage(sql + "成功!");
                        System.out.println(sql + "成功!");
                    } else {
                        sp.setSuccessful(false);
                        sp.setResponseMessage(sql + "失败!");
                        System.out.println(sql + "失败!");
                    }
                } else if (sql.toLowerCase().indexOf("select".toLowerCase()) >= 0) {
                    System.out.println("连接mariadb数据库,select...");
                    ResultSet rs = operateMariaDb.SelectData(sql);

                    try {
                        //创建一个JSONArray对象
                        JSONArray jsonArray = new JSONArray();
                        //获得ResultSetMeataData对象
                        ResultSetMetaData rsmd = rs.getMetaData();
                        if (rs.next()) {//当结果集存在时
                            while (rs.next()) {
                                //定义json对象
                                JSONObject obj = new JSONObject();
                                //判断数据类型&获取value
                                getType(rs, rsmd, obj);
                                //将对象添加到JSONArray中
                                jsonArray.put(obj);
                            }
                            System.out.println(jsonArray.toString().toLowerCase());
                            if (jsonArray.toString().toLowerCase().indexOf(expected) >= 0) {
                                sp.setSuccessful(true);
                                sp.setResponseMessage(sql + "成功!");
                                System.out.println(sql + "成功!");
                            } else {
                                sp.setSuccessful(false);
                                sp.setResponseMessage(sql + "失败!");
                                System.out.println(sql + "失败!");
                            }
                        } else {
                            sp.setSuccessful(false);
                            sp.setResponseMessage(sql + "查询为空!");
                        }
                    } catch (SQLException e) {
                        log.error("判断结果集是否为空时失败!");
                        e.printStackTrace();
                    }
                } else if (sql.toLowerCase().indexOf("update".toLowerCase()) >= 0) {
                    System.out.println("连接mariadb数据库,update...");
                    int ou = operateMariaDb.UpdateData(sql);
                    System.out.println("返回值是:" + ou);
                    if (ou != -1) {
                        sp.setSuccessful(true);
                        sp.setResponseMessage(sql + "成功!");
                        System.out.println(sql + "成功!");
                    } else {
                        sp.setSuccessful(false);
                        sp.setResponseMessage(sql + "失败!");
                        System.out.println(sql + "失败!");
                    }
                }
                break;
            case 3:
                System.out.println("sql:"+sql);
                if (sql.toLowerCase().indexOf("insert".toLowerCase()) >= 0) {
                    System.out.println("连接sqlserver数据库,insert...");
                    int oi = operateSqlServer.InsertData(sql);
                    System.out.println("返回值是:" + oi);
                    if (oi > 0) {
                        sp.setSuccessful(true);
                        sp.setResponseMessage(sql + "成功!");
                        System.out.println(sql + "成功!");
                    } else {
                        sp.setSuccessful(false);
                        sp.setResponseMessage(sql + "失败!");
                        System.out.println(sql + "失败!");
                    }
                } else if (sql.toLowerCase().indexOf("delete".toLowerCase()) >= 0) {
                    System.out.println("连接sqlserver数据库,delete...");
                    int od = operateSqlServer.DeleteData(sql);
                    System.out.println("返回值是:" + od);
                    if (od > 0) {
                        sp.setSuccessful(true);
                        sp.setResponseMessage(sql + "成功!");
                        System.out.println(sql + "成功!");
                    } else {
                        sp.setSuccessful(false);
                        sp.setResponseMessage(sql + "失败!");
                        System.out.println(sql + "失败!");
                    }
                } else if (sql.toLowerCase().indexOf("select".toLowerCase()) >= 0) {
                    System.out.println("连接sqlserver数据库,select...");
                    ResultSet rs = operateSqlServer.SelectData(sql);

                    try {
                        //创建一个JSONArray对象
                        JSONArray jsonArray = new JSONArray();
                        //获得ResultSetMeataData对象
                        ResultSetMetaData rsmd = rs.getMetaData();
                        if (rs.next()) {//当结果集存在时
                            while (rs.next()) {
                                //定义json对象
                                JSONObject obj = new JSONObject();
                                //判断数据类型&获取value
                                getType(rs, rsmd, obj);
                                //将对象添加到JSONArray中
                                jsonArray.put(obj);
                            }
                            System.out.println(jsonArray.toString().toLowerCase());
                            if (jsonArray.toString().toLowerCase().indexOf(expected) >= 0) {
                                sp.setSuccessful(true);
                                sp.setResponseMessage(sql + "成功!");
                                System.out.println(sql + "成功!");
                            } else {
                                sp.setSuccessful(false);
                                sp.setResponseMessage(sql + "失败!");
                                System.out.println(sql + "失败!");
                            }
                        } else {
                            sp.setSuccessful(false);
                            sp.setResponseMessage(sql + "查询为空!");
                        }
                    } catch (SQLException e) {
                        log.error("判断结果集是否为空时失败!");
                        e.printStackTrace();
                    }
                } else if (sql.toLowerCase().indexOf("update".toLowerCase()) >= 0) {
                    System.out.println("连接sqlserver数据库,update...");
                    int ou = operateSqlServer.UpdateData(sql);
                    System.out.println("返回值是:" + ou);
                    if (ou != -1) {
                        sp.setSuccessful(true);
                        sp.setResponseMessage(sql + "成功!");
                        System.out.println(sql + "成功!");
                    } else {
                        sp.setSuccessful(false);
                        sp.setResponseMessage(sql + "失败!");
                        System.out.println(sql + "失败!");
                    }
                }
                break;
            case 4:
                System.out.println("sql:"+sql);
                if (sql.toLowerCase().indexOf("insert".toLowerCase()) >= 0) {
                    System.out.println("连接oracle数据库,insert...");
                    int oi = operateOracle.InsertData(sql);
                    System.out.println("返回值是:" + oi);
                    if (oi > 0) {
                        sp.setSuccessful(true);
                        sp.setResponseMessage(sql + "成功!");
                        System.out.println(sql + "成功!");
                    } else {
                        sp.setSuccessful(false);
                        sp.setResponseMessage(sql + "失败!");
                        System.out.println(sql + "失败!");
                    }
                } else if (sql.toLowerCase().indexOf("delete".toLowerCase()) >= 0) {
                    System.out.println("连接oracle数据库,delete...");
                    int od = operateOracle.DeleteData(sql);
                    System.out.println("返回值是:" + od);
                    if (od > 0) {
                        sp.setSuccessful(true);
                        sp.setResponseMessage(sql + "成功!");
                        System.out.println(sql + "成功!");
                    } else {
                        sp.setSuccessful(false);
                        sp.setResponseMessage(sql + "失败!");
                        System.out.println(sql + "失败!");
                    }
                } else if (sql.toLowerCase().indexOf("select".toLowerCase()) >= 0) {
                    System.out.println("连接oracle数据库,select...");
                    ResultSet rs = operateOracle.SelectData(sql);

                    try {
                        //创建一个JSONArray对象
                        JSONArray jsonArray = new JSONArray();
                        //获得ResultSetMeataData对象
                        ResultSetMetaData rsmd = rs.getMetaData();
                        if (rs.next()) {//当结果集存在时
                            while (rs.next()) {
                                //定义json对象
                                JSONObject obj = new JSONObject();
                                //判断数据类型&获取value
                                getType(rs, rsmd, obj);
                                //将对象添加到JSONArray中
                                jsonArray.put(obj);
                            }
                            System.out.println(jsonArray.toString().toLowerCase());
                            if (jsonArray.toString().toLowerCase().indexOf(expected) >= 0) {
                                sp.setSuccessful(true);
                                sp.setResponseMessage(sql + "成功!");
                                System.out.println(sql + "成功!");
                            } else {
                                sp.setSuccessful(false);
                                sp.setResponseMessage(sql + "失败!");
                                System.out.println(sql + "失败!");
                            }
                        } else {
                            sp.setSuccessful(false);
                            sp.setResponseMessage(sql + "查询为空!");
                        }
                    } catch (SQLException e) {
                        log.error("判断结果集是否为空时失败!");
                        e.printStackTrace();
                    }
                } else if (sql.toLowerCase().indexOf("update".toLowerCase()) >= 0) {
                    System.out.println("连接oracle数据库,update...");
                    int ou = operateOracle.UpdateData(sql);
                    System.out.println("返回值是:" + ou);
                    if (ou != -1) {
                        sp.setSuccessful(true);
                        sp.setResponseMessage(sql + "成功!");
                        System.out.println(sql + "成功!");
                    } else {
                        sp.setSuccessful(false);
                        sp.setResponseMessage(sql + "失败!");
                        System.out.println(sql + "失败!");
                    }
                }
                break;
            case 5:
                System.out.println("sql:"+sql);
                if (sql.toLowerCase().indexOf("insert".toLowerCase()) >= 0) {
                    System.out.println("连接sybase数据库,insert...");
                    int oi = operateSybase.InsertData(sql);
                    System.out.println("返回值是:" + oi);
                    if (oi >= 0) {
                        sp.setSuccessful(true);
                        sp.setResponseMessage(sql + "成功!");
                        System.out.println(sql + "成功!");
                    } else {
                        sp.setSuccessful(false);
                        sp.setResponseMessage(sql + "失败!");
                        System.out.println(sql + "失败!");
                    }
                } else if (sql.toLowerCase().indexOf("delete".toLowerCase()) >= 0) {
                    System.out.println("连接sybase数据库,delete...");
                    int od = operateSybase.DeleteData(sql);
                    System.out.println("返回值是:" + od);
                    if (od > 0) {
                        sp.setSuccessful(true);
                        sp.setResponseMessage(sql + "成功!");
                        System.out.println(sql + "成功!");
                    } else {
                        sp.setSuccessful(false);
                        sp.setResponseMessage(sql + "失败!");
                        System.out.println(sql + "失败!");
                    }
                } else if (sql.toLowerCase().indexOf("select".toLowerCase()) >= 0) {
                    System.out.println("连接sybase数据库,select...");
                    ResultSet rs = operateSybase.SelectData(sql);

                    try {
                        //创建一个JSONArray对象
                        JSONArray jsonArray = new JSONArray();
                        //获得ResultSetMeataData对象
                        ResultSetMetaData rsmd = rs.getMetaData();
                        if (rs.next()) {//当结果集存在时
                            while (rs.next()) {
                                //定义json对象
                                JSONObject obj = new JSONObject();
                                //判断数据类型&获取value
                                getType(rs, rsmd, obj);
                                //将对象添加到JSONArray中
                                jsonArray.put(obj);
                            }
                            System.out.println(jsonArray.toString().toLowerCase());
                            if (jsonArray.toString().toLowerCase().indexOf(expected) >= 0) {
                                sp.setSuccessful(true);
                                sp.setResponseMessage(sql + "成功!");
                                System.out.println(sql + "成功!");
                            } else {
                                sp.setSuccessful(false);
                                sp.setResponseMessage(sql + "失败!");
                                System.out.println(sql + "失败!");
                            }
                        } else {
                            sp.setSuccessful(false);
                            sp.setResponseMessage(sql + "查询为空!");
                        }
                    } catch (SQLException e) {
                        System.err.println("判断结果集是否为空时失败!");
                        e.printStackTrace();
                    }
                } else if (sql.toLowerCase().indexOf("update".toLowerCase()) >= 0) {
                    System.out.println("连接sybase数据库,update...");
                    int ou = operateSybase.UpdateData(sql);
                    System.out.println("返回值是:" + ou);
                    if (ou != -1) {
                        sp.setSuccessful(true);
                        sp.setResponseMessage(sql + "成功!");
                        System.out.println(sql + "成功!");
                    } else {
                        sp.setSuccessful(false);
                        sp.setResponseMessage(sql + "失败!");
                        System.out.println(sql + "失败!");
                    }
                }
                break;
        }
        sp.sampleEnd();
        return sp;
    }


    /**
     * @param context
     * @desc teardown方法 该方法在测试结束后调用 用来释放变量等善后工作
     */
    @Override
    public void teardownTest(JavaSamplerContext context) {

        if (operateMariaDb != null) {
            operateMariaDb.ConnectClose();
        } else if (operateMysql != null) {
            operateMysql.ConnectClose();
        } else if (operateSqlServer != null) {
            operateSqlServer.ConnectClose();
        } else if (operateOracle != null) {
            operateOracle.ConnectClose();
        } else if (operateSybase != null) {
            operateSybase.ConnectClose();
        }
        end = System.currentTimeMillis();
        //可以在调试的时候自己打印时间
        System.out.println("开始时间: " + start + "毫秒");
        System.out.println("结束时间: " + end + "毫秒");
        //多少毫秒
        //log.debug("cost time:" + (end - start) / 1000);
        //多少秒
        System.out.println("共耗时:" + (end - start) + "毫秒");
        super.teardownTest(context);
    }

    private void getType(ResultSet rs, ResultSetMetaData rsmd,
                         JSONObject obj) throws SQLException {
        int total_rows = rsmd.getColumnCount();
        for (int i = 0; i < total_rows; i++) {
            String columnName = rsmd.getColumnLabel(i + 1);
            if (obj.has(columnName)) {
                columnName += "1";
            }
            try {
                switch (rsmd.getColumnType(i + 1)) {
                    case java.sql.Types.ARRAY:
                        obj.put(columnName, rs.getArray(columnName));
                        break;
                    case java.sql.Types.BIGINT:
                        obj.put(columnName, rs.getInt(columnName));
                        break;
                    case java.sql.Types.BOOLEAN:
                        obj.put(columnName, rs.getBoolean(columnName));
                        break;
                    case java.sql.Types.BLOB:
                        obj.put(columnName, rs.getBlob(columnName));
                        break;
                    case java.sql.Types.DOUBLE:
                        obj.put(columnName, rs.getDouble(columnName));
                        break;
                    case java.sql.Types.FLOAT:
                        obj.put(columnName, rs.getFloat(columnName));
                        break;
                    case java.sql.Types.INTEGER:
                        obj.put(columnName, rs.getInt(columnName));
                        break;
                    case java.sql.Types.NVARCHAR:
                        obj.put(columnName, rs.getNString(columnName));
                        break;
                    case java.sql.Types.VARCHAR:
                        obj.put(columnName, rs.getString(columnName));
                        break;
                    case java.sql.Types.TINYINT:
                        obj.put(columnName, rs.getInt(columnName));
                        break;
                    case java.sql.Types.SMALLINT:
                        obj.put(columnName, rs.getInt(columnName));
                        break;
                    case java.sql.Types.DATE:
                        obj.put(columnName, rs.getDate(columnName));
                        break;
                    case java.sql.Types.TIMESTAMP:
                        obj.put(columnName, rs.getTimestamp(columnName));
                        break;
                    default:
                        obj.put(columnName, rs.getObject(columnName));
                        break;
                }
            } catch (JSONException e) {
                e.printStackTrace();
            }
        }
    }

}
